Hands On: Modifying Reports

Hands On: Modifying Reports

In some cases, you will want to create reports that are more complex than the simple report you created in the previous exercise. Summarizing data, incorporating data from more than one database or providing multiple views of the same data are typical requirements for many, more complex reports. Seagate Crystal Reports has several powerful tools that help you design almost any report that you can imagine.

In this exercise, you'll start with an existing simple report, and use Seagate Crystal reports to make several modifications to the report.

Before you begin

Ensure that you have completed the previous exercise and know the name and location of the sample report you created. This will be used as the starting point for this exercise.

Step by step

	1	Open Seagate Crystal Reports 7, if it is not already running.
	2	Open the report you created in the previous exercise by selecting Open... under the File menu and navigating to the appropriate file location.
		Creating a Formula
		In many cases, the data you want to appear in your report already exists in fields within database tables. Sometimes, however, you need to put data in your report that does not exist in any of the data fields. In such cases, you must create a formula. For example, to calculate each customer’sales for last year as a percentage of total sales for the region, you need to know the subtotal of sales for the region as well as the sales for each individual customer in that region.
	3	Select the Design tab in the report window at the top left corner of the report. Select Formula Field... from the Insert menu item at the top of the screen. Click on the New... button, the Formula Name dialog box will appear. Name the formula "% of Sales" and click OK.

		The Formula Editor window will appear. The Formula Editor lets you create, test, and modify your formula. and displays a series of buttons representing the different types of Report Experts that are available.
	4	Enter the following formula in the Formula Editor window by double-clicking each field in the Formula Editor list boxes: {Customer.Last Year's Sales} % Sum ({Customer.Last Year's Sales}, {Customer.Region}) Double-click "Customer.Last Year's Sales" from the Fields list (Left Pane) Double-click "Per cent x%y" from the Operators list (Right Pane) Double-click "Group Footer #1:Sum of Last Year's Sales" from the Fields list (Left Pane) The operator you selected automatically formats the result of the formula to print as a percentage.

		When you select items from the list boxes, they are automatically entered in the formula window complete with brackets, punctuation, and other syntax items. If you enter your formula manually using the keyboard, you have to make certain you enter those syntax items yourself. It is safer and faster to build a formula by choosing list-box items. Scroll through the Functions list box to see the wide range available to you. Functions are built-in procedures or subroutines used to evaluate, calculate, or transform data; they make it easy for you to create formulas without coding. The Formula Editor includes financial functions that let you place arrays and ranges in formulas. You can even extend functions with your own custom functions by creating them with any COM-compliant language such as Visual Basic, Visual C++, and Delphi.
	5	Click on the Save and Close button (third from the left) at the top of the Formula Editor to return to your report.
	6	Click Insert to place the field in your report. When you move the pointer, it will change to a gray field object box.
	7	Position the field object box in the Details section of your report, to the right of the "Last Year's Sales" column. Click to insert the field.
	8	Click Close. The Insert Fields dialog box will close.
		You have now created a new field in your report which uses a simple formula to calculate the percentage contribution of each customer to the total sales for the region. Seagate Crystal Reports has over 160 built-in formula functions which allow you to perform a wide range of calculations on data in your database.
		Highlighting Important Data
		To manipulate the formatting in different sections of your report, you can use formulas created in the Formula Editor to control field and object attributes such as color, font, border, underline, and strikeout. Using formulas, you can conditionally format data based on whatever criteria you select. This version of Seagate Crystal Reports also introduces the concept of highlighting - a quick and easy way to identify important data in your report. In the following example we'll use highlighting to display percentages of last year’s sales in red if they are less than 20% and in blue if they are greater than 30%.
	9	Select the Preview tab in the report window. A preview of your report will appear.
	10	Position the mouse pointer over any number in the "% of Sales" column and click to select it. A rectangle appears around that entry and the rest of the column becomes shaded to indicate that all similar fields in this column have also been selected.
	11	Click the right mouse button. The shortcut menu for that column appears.
	12	Select Highlighting Expert... from the shortcut menu. The Highlighting Expert window will appear.
	13	In the Item List window, click on new item to create a new rule. Select "less than" from the Value is: list box and type the number 20 in the value box. In the Font Color list box, select "Red". This completes the first condition we wanted to highlight. Now, again click on new item in the Item List window to create the next rule. Select "greater than" from the Value is: list box and type the number 30 in the value box. In the Font Color list box, select "Blue". This completes the second condition.

	14	Click OK to save the new highlighting and return to the Preview window. You'll now notice that all sales percentages less than 20% or greater than 30% are now highlighted in the appropriate color. Any other value is displayed in the default color. Highlighting is a very simple way to spot important values, especially in very complex or detailed reports.
		Changing Databases
		Once you've developed a report, it's often necessary to adapt the report to use different databases for a variety of reasons. Reports may be developed and tested using a prototype database and then deployed in a production environment. It might be necessary to change the structure of the database or even the type of database or method of connection. This can result in changes to database names, field names or database drivers, any of which could cause a report to fail to run correctly. Seagate Crystal Reports includes a special Field Mapping Expert that helps you adapt existing reports to run against new databases without having to recreate the report. The next example will show you how this accomplished by changing the database used for the simple sales report you created.

Note

This example uses a sample database which is not installed in the same directory as the Seagate Crystal Reports program files. In order to make this exercise easier to complete, you may want to copy the small database file to the Seagate Crystal Reports directory. To do this, simply right-click on the Sample Database icon on the right and select either Save Target As... or Save Link As... depending on your browser. Navigate to the Seagate Crystal Reports program directory (The default install path is "Program Files\Seagate Software\Crystal Reports") and click Save. You can remove the file when completed this exercise.

15	Select Set Location... from the Database menu item at the top of the screen. Since you want to specify a new location for the database, click on the Set Location... button. The Choose Database File window will appear.
16	Select "All Files" from the Files of type: list box. Now you'll need to navigate to proper location of the new database (see note above). The name of the new database file is "Xtreme_2". Select this file and click Open. The Choose Database File window will appear, click Done to complete the change.
17	Because the new database is slightly different from the old database, you will receive a warning message. Click on Yes to continue. The Field Mapping Expert window will appear.

	This window is divided into four panes. The top-left pane shows fields in the existing report which have no matching fields in the new database (unresolved fields). The top-right pane shows fields that are available in the new database. The two bottom panes show fields that have been "mapped" automatically because the field names and types of data seem to match between the report and the new database.
18	To complete the field mapping process you'll now manually map the remaining unresolved fields in the report. This involves selecting one of the report fields in the top-left pane, selecting the appropriate new database field in the top-right pane and clicking Map to link them. Map the fields as shown in the table below:
		Report Field	New Database Field
		Customer Name	Company Name
		Last Year's Sales	FY98 Sales Total
		Region	State/County
	The Field Mapping Expert only shows database fields that are the same type as the selected report fields. In this case, "Last Year's Sales" is a numeric field, so only database fields which are also numeric fields are displayed in the top-right pane. This "type checking" can be turned off by un-checking the Match Type checkbox in the expert.
19	Click OK to return to the Preview window. The Field Mapping Expert has now re-mapped all of the report fields to their corresponding fields in the new database. You can confirm this by passing your mouse pointer over one of the sales numbers and seeing the new field name "FY98 Sales Total" appear in the ToolTip.
	Inserting a Map
	Sometimes, there are important relationships in your report that depend on geographic location. These dependencies are often very difficult to uncover using a report with conventional tables and graphs. Seagate Crystal reports provides a geographic mapping expert that provides a visual view of your data, superimposed on an appropriate map. In this exercise, we'll add a map to the simple sales report and interactively alter the appearance of the map.
20	Before adding the map, locate the pie graph at the beginning of the report, right-click on it and select Delete.
21	Select TopN/Sort Group Expert... from the Report menu item at the top of the screen. Change the "TopN" item to "Sort All" and click on the OK button.
22	Select Map... from the Insert menu item at the top of the screen. The Map Expert window will appear. The default values shown in the Data window will map the Sum of FY98 Sales by State/County. We'll accept these defaults.
23	Click on the Type tab at the top of the window. You can change the type of map produced as well as set the colors used to display data. We'll accept the rest of the default values, but we will change to colors used to display the data. From the "Color of lowest interval" box, select the color in the bottom row labeled "Pale green". In the "Color of highest interval" box, select the color in the top row labeled "Dark green". Click OK to continue.

	Seagate Crystal Reports analyzes the "State/County" field and determines that it contains data for all of the states in the United States. It then retrieves the appropriate map and shades each state from pale green to dark green based on the magnitude of sales. Like charts, maps support drill-down in Preview mode. If you double-click on one of the states in the map, you will be presented with underlying sales detail for that state.
24	Right-click on the map and select Launch Analyzer... from the menu. This will open a third tabbed window which presents the Analyzer view of the report. In the Analyzer, you can right-click to zoom in, zoom out and pan the map to zero in a particular region. Try zooming in on Southern California and you will eventually see additional detail representing cities, roads and rivers. Any changes you make in this view are retained in the Preview window, letting you interactively customize the map view presented without recreating the map itself.

There are many other advanced modifications you can make to reports. This includes the use of cross-tabs or subreports to examine different views of the same data. Reports can be created which use built-in SQL query features to perform report processing on the database server. Or reports can complied for distribution to others. To see some of the other powerful capabilities of Seagate Crystal Reports 7, take some time to browse the User's Guide or explore the on-line documentation in the help system.

For the latest information about this product, please visit the web site at www.seagatesoftware.com/crystalreports.

Copyright ⌐ 1998 Seagate Software Inc. All rights reserved. Click here for additional information.